/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.just.dusin.ooii.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.just.dusin.ooii.util.DateUtil;
import org.just.dusin.ooii.util.NumberUtil;


/**
 *
 * @author Administrator
 */
public class YearPlanDao {

    private Connection conn = null;
    public int size;

    public YearPlanDao(Connection conn) {
        this.conn = conn;
    }

    public boolean doCreate(YearPlanBean yearPlanBean) throws Exception {
        boolean flag = false;
        PreparedStatement pstmt = null;
        String sql = "INSERT INTO [yearplan]([orgcode],[year],[yearplan],[monthplan],[dayplan],[hourplan],[rate],[note])"
                + " VALUES (?,?,?,?,?,?,?,?)";
        try {
            pstmt = this.conn.prepareStatement(sql);

            pstmt.setString(1, yearPlanBean.getOrgCode());
            pstmt.setInt(2, yearPlanBean.getYear());
            pstmt.setDouble(3, yearPlanBean.getYearPlan());
            pstmt.setDouble(4, yearPlanBean.getMonthPlan());
            pstmt.setDouble(5, yearPlanBean.getDayPlan());
            pstmt.setDouble(6, yearPlanBean.getHourPlan());
            pstmt.setDouble(7, yearPlanBean.getRate());
            pstmt.setString(8, yearPlanBean.getNote());

            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }

    public boolean doUpdate(YearPlanBean yearPlanBean) throws Exception {
        boolean flag = false;
        PreparedStatement pstmt = null;
        String sql = "UPDATE [yearplan] SET [orgcode]=?,[year]=?,[yearplan]=?,[monthplan]=?,[dayplan]=?,[hourplan]=?,[rate]=?,[note]=?  WHERE id=?";
        try {
            pstmt = this.conn.prepareStatement(sql);

            pstmt.setString(1, yearPlanBean.getOrgCode());
            pstmt.setInt(2, yearPlanBean.getYear());
            pstmt.setDouble(3, yearPlanBean.getYearPlan());
            pstmt.setDouble(4, yearPlanBean.getMonthPlan());
            pstmt.setDouble(5, yearPlanBean.getDayPlan());
            pstmt.setDouble(6, yearPlanBean.getHourPlan());
            pstmt.setDouble(7, yearPlanBean.getRate());
            pstmt.setString(8, yearPlanBean.getNote());

            pstmt.setLong(9, yearPlanBean.getId());
            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }
    public boolean doUpdate(CLCSBean clcs) throws Exception {
        boolean flag = false;
        PreparedStatement pstmt = null;
        String sql = "UPDATE [yearplan] SET [yearplan]=?,[monthplan]=?,[dayplan]=?,[hourplan]=?,[rate]=?,[updateTime]=?  WHERE orgCode=? and year=? ";
        try {
            pstmt = this.conn.prepareStatement(sql);

            
            pstmt.setDouble(1, clcs.getYearPlan());
            pstmt.setDouble(2, clcs.getMonthPlan());
            pstmt.setDouble(3, clcs.getDayPlan());
            pstmt.setDouble(4, clcs.getHourPlan());
            pstmt.setDouble(5, clcs.getRate());
            pstmt.setString(6, DateUtil.get(clcs.getXgrq()));

            pstmt.setLong(7, clcs.getId());
            pstmt.setShort(8, clcs.getYear());
            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }

    public boolean doDelete(long pid) throws Exception {
        boolean flag = false;
        PreparedStatement pstmt = null;
        String sql = "DELETE FROM yearplan WHERE id=?";
        try {
            pstmt = this.conn.prepareStatement(sql);
            pstmt.setLong(1, pid);
            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }

    public boolean doDelete(String[] pids) throws Exception {
        for (String pid : pids) {
            long id = Long.parseLong(pid);
            doDelete(id);
        }
        return true;
    }

   public YearPlanBean findById(long pid) throws Exception {
        YearPlanBean yearPlan = null;
        PreparedStatement pstmt = null;
        String sql = "select yearplan.[id],yearplan.[orgcode],smallname,[year],[yearplan],[monthplan],[dayplan],[hourplan],[rate],yearplan.[note],updatetime from yearplan  left join org  on yearplan.orgcode=org.orgcode where yearplan.orgcode =? and year = ?";
        try {
            pstmt = this.conn.prepareStatement(sql);
            pstmt.setLong(1, pid);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                yearPlan = new YearPlanBean();
                yearPlan.setId(rs.getLong(1));
                yearPlan.setOrgCode(rs.getString(2));
                yearPlan.setSmallName(rs.getString(3));
                yearPlan.setYear(rs.getShort(4));
                
                double yearPlanValue = rs.getDouble(5);
                yearPlanValue = NumberUtil.fixed(yearPlanValue/1000);
                yearPlan.setYearPlan(yearPlanValue);
                
                double monthPlan = rs.getDouble(6);
                monthPlan = NumberUtil.fixed(monthPlan/1000);
                yearPlan.setMonthPlan(monthPlan);
                
                yearPlan.setDayPlan(rs.getDouble(7));
                yearPlan.setHourPlan(rs.getDouble(8));
                yearPlan.setRate(rs.getDouble(9));
                yearPlan.setNote(rs.getString(10));
                Date time = rs.getTimestamp(11);
                String stime = DateUtil.get(time);
                yearPlan.setUpdateTime(stime);
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return yearPlan;
    }

   public YearPlanBean findByOrgCode_Year(String orgCode ,int year) throws Exception {
        YearPlanBean yearPlan = null;
        PreparedStatement pstmt = null;
        String sql = "select yearplan.[id],yearplan.[orgcode],smallname,[year],[yearplan],[monthplan],[dayplan],[hourplan],[rate],yearplan.[note],updatetime from yearplan  left join org  on yearplan.orgcode=org.orgcode where yearplan.orgcode =? and year = ?";
        try {
            pstmt = this.conn.prepareStatement(sql);
            pstmt.setString(1, orgCode);
            pstmt.setInt(2, year);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                yearPlan = new YearPlanBean();
                yearPlan.setId(rs.getLong(1));
                yearPlan.setOrgCode(rs.getString(2));
                yearPlan.setSmallName(rs.getString(3));
                yearPlan.setYear(rs.getShort(4));
                
                double yearPlanValue = rs.getDouble(5);
                yearPlanValue = NumberUtil.fixed(yearPlanValue/1000);
                yearPlan.setYearPlan(yearPlanValue);
                
                double monthPlan = rs.getDouble(6);
                monthPlan = NumberUtil.fixed(monthPlan/1000);
                yearPlan.setMonthPlan(monthPlan);
                
                yearPlan.setDayPlan(rs.getDouble(7));
                yearPlan.setHourPlan(rs.getDouble(8));
                yearPlan.setRate(rs.getDouble(9));
                yearPlan.setNote(rs.getString(10));
                Date time = rs.getTimestamp(11);
                String stime = null;
                if(time!=null)
                    stime = DateUtil.get(time);
                yearPlan.setUpdateTime(stime);
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return yearPlan;
    }
   
    public List<YearPlanBean> findAll(String orgCode, int beginIndex, int size) throws Exception {
        List<YearPlanBean> all = new ArrayList<YearPlanBean>();
        PreparedStatement pstmt = null;
        PreparedStatement pstmtsize = null;
        String sqlsize = "select count(*) from yearplan where orgcode like '" + orgCode + "%'";

        String sql = "select [id],[orgcode],smallname,[year],[yearplan],[monthplan],[dayplan],[hourplan],[rate],[note],updatetime FROM (select yearplan.[id],yearplan.[orgcode],smallname,[year],[yearplan],[monthplan],[dayplan],[hourplan],[rate],yearplan.[note],updatetime,row_number() over(order by yearplan.id) rn from yearplan  left join org  on yearplan.orgcode=org.orgcode where yearplan.orgcode like '" + orgCode + "%') t where rn >=? and rn<?";

        try {
            pstmtsize = this.conn.prepareStatement(sqlsize);
            ResultSet rssize = pstmtsize.executeQuery(); //
            rssize.next();
            this.size = rssize.getInt(1);

            pstmt = this.conn.prepareStatement(sql);
            pstmt.setInt(1, beginIndex + 1);
            pstmt.setInt(2, beginIndex + size + 1);
            ResultSet rs = pstmt.executeQuery(); //
            YearPlanBean yearPlan = null;
            while (rs.next()) { //
                yearPlan = new YearPlanBean();
                yearPlan.setId(rs.getLong(1));
                yearPlan.setOrgCode(rs.getString(2));
                yearPlan.setSmallName(rs.getString(3));
                yearPlan.setYear(rs.getShort(4));
                
               double yearPlanValue = rs.getDouble(5);
                yearPlanValue = NumberUtil.fixed(yearPlanValue/1000);
                yearPlan.setYearPlan(yearPlanValue);
                
                double monthPlan = rs.getDouble(6);
                monthPlan = NumberUtil.fixed(monthPlan/1000);
                yearPlan.setMonthPlan(monthPlan);
                
                
                yearPlan.setDayPlan(rs.getDouble(7));
                yearPlan.setHourPlan(rs.getDouble(8));
                yearPlan.setRate(rs.getDouble(9));
                yearPlan.setNote(rs.getString(10));
                Date time = rs.getTimestamp(11);
                String stime = DateUtil.get(time);
                yearPlan.setUpdateTime(stime);
                all.add(yearPlan); //
//System.out.println(all.size());
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return all;
    }

    public Date getCurrentTime(String orgCode,int year) throws Exception {
        Date rtDate = null;
        PreparedStatement pstmt = null;
        String sql = "select updatetime from  yearplan  WHERE orgCode=? and year=?";
        try {
            pstmt = this.conn.prepareStatement(sql);
            pstmt.setString(1, orgCode);
            pstmt.setShort(2, (short)year);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                Date updatetime = rs.getTimestamp(1);      
                rtDate = updatetime;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return rtDate;
    }

    public int getTotalSize() {
        return size;
    }
}
